# -*- coding: utf-8 -*-
import pandas as pd
from datetime import date

from django.core.serializers import serialize
from django.db import connection
from django.db.models import QuerySet, FilteredRelation, Q, F, Max, Subquery, OuterRef, Window
from rest_framework import status, serializers
from rest_framework.decorators import action
from rest_framework.filters import SearchFilter, OrderingFilter
from rest_framework.response import Response
from rest_framework.viewsets import ModelViewSet

from django_example.test_http_class import logger
from django_package.portfolio.models import Portfolio, Valuation, Indicator
from django_service.messages import default_response


class PortfolioView(ModelViewSet):
    queryset = Portfolio.objects.filter(portfolio_status=1).order_by('establish_date')

    authentication_classes = []
    permission_classes = []

    serializer_class = serializers.Serializer

    @action(methods=['post'], detail=False)
    def get_port_list(self, *args, **kwargs):
        # valuation_date = self.request.data.get('valuation_date')
        # valuation_date = date.today()
        valuation_date = '2024-10-28'
        last_valuation = '2024-10-28'
        queryset = self.get_queryset()
        queryset = queryset.filter(indicator__valuation_date=last_valuation).values(
            indicator__total_amount=F('indicator__total_amount'), total_amount=F('indicator__total_amount'))

        queryset = queryset.annotate(
            v=FilteredRelation('valuation', condition=Q(valuation__valuation_date=valuation_date)),
            max_valuation=F('v__valuation_date'),
            p_indicator=FilteredRelation('indicator',
                                         condition=Q(indicator__valuation_date=F('max_valuation'))),
            p_indicator__sharpe_ratio=F('p_indicator__sharpe_ratio'),
        )

        # queryset = queryset.annotate(
        #     # v=FilteredRelation('valuation', condition=Q(valuation__valuation_date__lte=valuation_date)),
        #     v=FilteredRelation('valuation', condition=Q(valuation__valuation_date=valuation_date)),
        # )
        # queryset = queryset.annotate(
        #     # max_valuation=F('valuation__valuation_date'),
        #     max_valuation=F('v__valuation_date'),
        #     p_indicator=FilteredRelation('indicator',
        #                                  condition=Q(indicator__valuation_date=F('max_valuation'),
        #                                              indicator__asset_scope='PORTFOLIO'))
        # )
        # queryset = queryset.annotate(
        #     portfolio_sharpe_ratio=F('p_indicator__sharpe_ratio'),
        # )

        queryset.values('portfolio_code', 'max_valuation', 'p_indicator__sharpe_ratio')

        old_sql = """LEFT OUTER JOIN `port_valuation` v ON (`port_portfolio`.`portfolio_code` = v.`portfolio_code` AND (v.`valuation_date` = %s))"""
        new_sql = """LEFT OUTER JOIN ( SELECT `portfolio_code`, MAX(`valuation_date`) AS `valuation_date` FROM `port_valuation` WHERE `valuation_date` <= %s GROUP BY `portfolio_code` ) v ON (`port_portfolio`.`portfolio_code` = v.`portfolio_code`)"""
        sql, params = queryset.query.sql_with_params()
        sql = sql.replace(old_sql, new_sql)
        logger.info(f'sql:{sql % params}')
        raw_qs = queryset.raw(sql, params)
        with connection.cursor() as cursor:
            cursor.execute(sql, params)
            columns = [column[0] for column in cursor.description]
            result = [dict(zip(columns, row)) for row in cursor.fetchall()]
        # queryset = queryset.annotate(
        #     # v=FilteredRelation('valuation', condition=Q(valuation__valuation_date__lte=valuation_date)),
        #     v=FilteredRelation('valuation', condition=Q(valuation__valuation_date=valuation_date)),
        # )
        # queryset = queryset.values('portfolio_code')
        # queryset = queryset.annotate(max_date=Max('v__valuation_date'))
        # subquery = Subquery(
        #     Valuation.objects.filter(
        #         portfolio_code=OuterRef('portfolio_code')
        #     ).annotate(
        #         max_date=Max('v__valuation_date')
        #     ).order_by('+valuation_date').first()
        # )
        # queryset = queryset.annotate(
        #     p_indicator=FilteredRelation('indicator',
        #                                  condition=Q(indicator__valuation_date=F('valuation_date'),
        #                                              indicator__asset_scope='PORTFOLIO')))

        sql, params = queryset.query.sql_with_params()

        return Response(data=default_response(), status=status.HTTP_200_OK)

    @action(methods=['post'], detail=False)
    def get_curve(self, *args, **kwargs):
        """
        获取时序曲线
        :param args:
        :param kwargs:
        :return:
        """
        start_date = ''
        end_date = ''
        curve_type = ''
        self.get_queryset().annotate(
            port_indicator=FilteredRelation('indicator', condition=Q(indicator__valuation_date_gt=start_date,
                                                                     indicator__valuation_date_le=end_date,
                                                                     indicator__asset_scope='PORTFOLIO')),
        ).filter()

        # 日期补齐，使用0填充空缺日期
        date_index = pd.date_range(start='2020-01-01', end='2020-12-01', freq='D', name='date_column').date

        return Response(data=default_response(), status=status.HTTP_200_OK)

    @action(methods=['post'], detail=False)
    def get_split_curve(self, *args, **kwargs):
        """
        获取按不同指标划分的时序曲线
        :param args:
        :param kwargs:
        :return:
        """
        start_date = ''
        end_date = ''
        curve_type = ''
        split_type = ''
        self.get_queryset().annotate(
            port_indicator=FilteredRelation('indicator', condition=Q(indicator__valuation_date_gt=start_date,
                                                                     indicator__valuation_date_le=end_date,
                                                                     indicator__asset_scope='PORTFOLIO')),
        ).filter()

        date_index = pd.date_range(start='2020-01-01', end='2020-12-01', freq='D', name='date_column').date

        return Response(data=default_response(), status=status.HTTP_200_OK)

    @action(methods=['post'], detail=False)
    def get_ratio(self, *args, **kwargs):
        """
        获取截面占比
        :param args:
        :param kwargs:
        :return:
        """
        data_date = '2024-10-28'
        self.get_queryset().annotate(
            port_position=FilteredRelation('position', condition=Q(indicator__valuation_date=data_date,
                                                                   indicator__asset_scope='PORTFOLIO')),
        ).filter()
        return Response(data=default_response(), status=status.HTTP_200_OK)
